﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Text;

namespace TrackingAssist.WebTrends
{
    public class ExcelSpec : IWebTrendsSpec
    {
        //public IList<Atlas> LoadSpecFromFile(string fileName)
        public void LoadSpecFromFile(string fileName, out IList<WebTrends> specAtlasList)
        {
            specAtlasList = new List<WebTrends>();
            
            if (string.IsNullOrEmpty(fileName))
            {
                return;
            }
            

            string strConnectionString;
            OleDbConnection cn = null;
            OleDbDataAdapter daAdapter = null;

            try
            {
                strConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties=\"Excel 12.0;HDR=YES\"";
                cn = new OleDbConnection();
                cn.ConnectionString = strConnectionString;
                cn.Open();

                string strComand;

                strComand = string.Format("select ID,Value,Description,Type from [{0}$]", WebTrendsConsts.SPEC_FLAG_SHEETNAME);
                
                //get atlas spec
                daAdapter = new OleDbDataAdapter(strComand, cn);
                DataTable dt = new DataTable();
                daAdapter.FillSchema(dt, SchemaType.Source);
                daAdapter.Fill(dt);

                foreach (DataRow row in dt.Rows)
                {
                    string desc = row[WebTrendsConsts.SPEC_DESCRIPTION_COLUMN_NUMBER].ToString().Trim();
                    if (string.Equals(desc, string.Empty) || string.Equals(desc, WebTrendsConsts.SPEC_FLAG_SKIP))
                        continue;
                    if (string.Equals(desc, WebTrendsConsts.SPEC_FLAG_EMPTY))
                        break;

                    string value = row[WebTrendsConsts.SPEC_VALUE_COLUMN_NUMBER].ToString().Trim();
                    WebTrends spec = FormatExcelSpecValue(value, desc);
                    if (spec != null)
                        specAtlasList.Add(spec);
                }


            }
            catch (Exception ex)
            {
                return;
            }
            finally
            {
                if (cn != null && cn.State == ConnectionState.Open)
                    cn.Close();
            }
        }


        protected static WebTrends FormatExcelSpecValue(string value, string description)
        {
            string[] item = value.Split(new char[] { '|' });
            if (item.Length >= 10)
            {
                //if section&&location&&action are all empty, return as not atlas tracking;
                if (string.IsNullOrEmpty(item[3]) && string.IsNullOrEmpty(item[4]) && string.IsNullOrEmpty(item[1]))
                    return null;

                WebTrends webtrends = new WebTrends();
                webtrends.Id = item[0].Trim();
                WebTrendsSpecUtility.SetSpecAtlasItem(webtrends.Action, item[1]);
                WebTrendsSpecUtility.SetSpecAtlasItem(webtrends.ActionName, item[2]);
                WebTrendsSpecUtility.SetSpecAtlasItem(webtrends.Section, item[3]);
                WebTrendsSpecUtility.SetSpecAtlasItem(webtrends.Location, item[4]);
                WebTrendsSpecUtility.SetSpecAtlasItem(webtrends.Var3, item[7]);
                WebTrendsSpecUtility.SetSpecAtlasItem(webtrends.Var1, item[8]);
                WebTrendsSpecUtility.SetSpecAtlasItem(webtrends.Var2, item[9]);
                webtrends.Description = description;
                return webtrends;
            }
            return null;
        }


       
    }
}
